<?php
/*
 * $Id$
 *
 *
 */

if (!defined('BASEPATH')) exit('No direct script access allowed');

class Primanota extends MX_Controller {
	var $template = array();

	function __construct()
	{
		parent::__construct();
		$this->template['module']	= 'adozioni';
		$this->template['nome_mese'] = array(1 => 'Gennaio', 'Febbraio', 'Marzo', 'Aprile', 'Maggio', 'Giugno', 'Luglio', 'Agosto', 'Settembre', 'Ottobre', 'Novembre', 'Dicembre'); 
		$this->load->model('adozioni_model', 'ado');
        $this->load->helper('adozioni');

	}

	
	function index()
	{
		$this->template['title'] = __("Primanota", "adozioni");
		$this->template['anno'] = date("Y");
		$this->template['mese'] = date("n");
        $this->template['arr_mese'] = array(1,2,3,4,5,6,7,8,9,10,11,12);
		$this->template['anni'] = $this->ado->get_anni();

		
		$this->layout->load($this->template, 'contabilita_primanota_index');
	}

	function show()
	{
		if(!$this->input->post('anno'))
		{
			echo "Errore : Anno non specificato";
			exit;
		}


		if($this->input->post('mese'))
		{
			$where_mese = "and extract(month from data)=".$this->input->post('mese');
		}

		$anno = $this->input->post('anno');
		$mese = $this->input->post('mese');

		$where_clause = "extract(year from data)=$anno $where_mese";

		header('Content-type:application/vnd.ms-excel');
		header('Content-disposition: filename="Primanota_'. $anno . '_' . $this->template['nome_mese'][ $mese ] .  '.xls"');

		#Prendo i conti
		$conti = array();
		$sql = "select id,nome from conti";
		$query = $this->db->query($sql);
		$n = $query->num_rows();
		$rows = $query->result_array();
		for($i=0;$i<$n;$i++)
		{
			$id = $rows[$i]['id'];
			$conti["$id"]['nome'] = $rows[$i]['nome'];
			$conti["$id"]['saldo']= $rows[$i]['nome'];
		}




		#Intestazione
			#riga 1
		echo "Data\tConto\tSostenitore\tMissione\tCategoria\t";
		foreach($conti as $k=>$v)
		{
			$nomeconto = $conti["$k"]['nome'];
			echo"\t\t$nomeconto\t\t";
		}
		echo "\n";
			#riga 2
		echo "\t\t\t\t\t";
		$n = sizeof($conti);
		for($i=0;$i<$n;$i++)
		{
			echo "\tEntrate\tUscite\tSaldo\t";
		}
		echo "\n";


		# Dati
		$sql = "
			select o.data,o.id_conto,o.valore as entrate,0.0 as uscite,s.nomecognome as sostenitore 
			from offerte o,sostenitori s 
			where o.id_sostenitore = s.id and $where_clause
			union 
			select data,id_conto,0.0 as entrate,valore as uscite,null as sostenitore 
			from versamenti 
			where $where_clause;
		";

		$sql = "
			select o.data,o.id_conto,o.valore as entrate,0.0 as uscite,s.nomecognome as sostenitore,m.nome as missione,cat.nome || '/' || scat.nome as categoria 
			from offerte o,sostenitori s,missioni m,categorie cat,sottocategorie scat 
			where o.id_sostenitore = s.id and o.id_missione=m.id and o.id_categoria=cat.id and o.id_sottocategoria=scat.id and $where_clause
			union 
			select v.data,v.id_conto,0.0 as entrate,v.valore as uscite,null as sostenitore,m.nome as missione,null as categoria 
			from versamenti v,missioni m 
			where v.id_missione=m.id and $where_clause;
		";

		$table_name = "pr".time();


		$sql = "create table $table_name(data date,id_conto integer,entrate float,uscite float,sostenitore varchar(150),missione varchar(100),categoria varchar(100))";
		#echo $sql;
		$this->db->query($sql);

		$sql1 = "insert into $table_name 	
			select o.data,o.id_conto,o.valore as entrate,0.0 as uscite,s.nomecognome as sostenitore,m.nome as missione,cat.nome || '/' || scat.nome as categoria 
			from offerte o,sostenitori s,missioni m,categorie cat,sottocategorie scat 
			where o.id_sostenitore = s.id and o.id_missione=m.id and o.id_categoria=cat.id and o.id_sottocategoria=scat.id and $where_clause
		";

		$this->db->query($sql1);

		$sql2 = "insert into $table_name
		select v.data,v.id_conto,0.0 as entrate,v.valore as uscite,null as sostenitore,m.nome as missione,null as categoria 
			from versamenti v,missioni m 
			where v.id_missione=m.id and $where_clause
		";

		$this->db->query($sql2);

		$sql = "select * from $table_name order by data";

		$query = $this->db->query($sql);
		$n = $query->num_rows();
		$rows = $query->result_array();
		for($i=0;$i<$n;$i++)
		{
			$r = $rows[$i];
			$id_conto = $r['id_conto'];
			$nomeconto= $conti["$id_conto"]['nome'];
			echo "$r[data]\t$nomeconto\t$r[sostenitore]\t$r[missione]\t$r[categoria]\t";
			
			foreach($conti as $k=>$v)
			{
				echo "\t";
				if($k == $id_conto)
				{
					$conti["$k"]['saldo'] = round($conti["$k"]['saldo'] + $r['entrate'] - $r['uscite'],2);
					$r['entrate'] = format_euro($r['entrate']);
					$r['uscite'] = format_euro($r['uscite']);
					echo "$r[entrate]\t$r[uscite]\t";
				}
				else
				{
					echo "0,00\t0,00\t";
				}
				echo format_euro($conti["$k"]['saldo'])."\t";
			}
			echo "\n";
		}

		$sql = "drop table $table_name";
		$this->db->query($sql);	
	}
}
